﻿using Microsoft.SqlServer.Management.Smo;
using SQLDocumentor.SQLModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SQLDocumentor.SQLHelpers
{
    public class SQLInfoEventArgs
    {
        public string Message { get; set; }
    }
    public delegate void SQLEventHandler(object sender, SQLInfoEventArgs fea);
    public class SQLServerLoader
    {
        private static SQLServerLoader _current;
        public static event SQLEventHandler SQLEvent;
        public static SQLServerLoader Current
        {
            get
            {
                if (_current == null)
                    _current = new SQLServerLoader();
                return _current;
            }
        }
        private static void OnSQLEvent(String Message)
        {
            if (SQLEvent != null)
            {
                SQLInfoEventArgs fea = new SQLInfoEventArgs();
                fea.Message = Message;
                SQLEvent(null, fea);
            }
        }
        public SQLServer LoadSqlServer(SQLDocumentOption option)
        {
            SQLServer srv = new SQLServer();
            Server server = new Server(option.Instance);
            OnSQLEvent("Load " + option.Instance + "  Informations");
            server.ConnectionContext.LoginSecure = !option.SQLAuthentication;
            if (option.SQLAuthentication)
            {
                server.ConnectionContext.Login = option.UserNameSQL;
                server.ConnectionContext.Password = option.PasswordSQL;
            }
            srv.Version = server.Version;
            srv.Product = server.Product;
            srv.ProductLevel = server.ProductLevel;
            srv.Edition = server.Edition;
            srv.BuildClrVersion = server.BuildClrVersion;
            srv.ServiceAccount = server.ServiceAccount;
            srv.ServiceInstanceId = server.ServiceInstanceId;
            srv.ServiceName = server.ServiceName;
            srv.ServiceStartMode = server.ServiceStartMode.ToString();
            srv.RootDirectory = server.RootDirectory;
            srv.Collation = server.Collation;
            srv.CollationID = server.CollationID;
            server.SetDefaultInitFields(typeof(StoredProcedure), true);
            server.SetDefaultInitFields(typeof(Table), true);
            server.SetDefaultInitFields(typeof(Database), true);
            server.SetDefaultInitFields(typeof(Column), true);
            srv.Databases = FillDatabases(server.Databases);

            return srv;


        }

        private SQLDatabase[] FillDatabases(DatabaseCollection Databases)
        {
            List<SQLDatabase> lstDb = new List<SQLDatabase>();
            foreach (Database db in Databases)
            {
                if (db.IsSystemObject)
                    continue;
                OnSQLEvent("Load database " + db.Name + "  Informations");
                SQLDatabase sqldb = new SQLDatabase();
                sqldb.AnsiNullDefault = db.AnsiNullDefault;
                sqldb.AnsiNullsEnabled = db.AnsiNullsEnabled;
                sqldb.AnsiPaddingEnabled = db.AnsiPaddingEnabled;
                sqldb.AnsiWarningsEnabled = db.AnsiWarningsEnabled;
                sqldb.ArithmeticAbortEnabled = db.ArithmeticAbortEnabled;
                sqldb.AutoClose = db.AutoClose;
                sqldb.AutoCreateStatisticsEnabled = db.AutoCreateStatisticsEnabled;
                sqldb.AutoShrink = db.AutoShrink;
                sqldb.AutoUpdateStatisticsAsync = db.AutoUpdateStatisticsAsync;
                sqldb.AutoUpdateStatisticsEnabled = db.AutoUpdateStatisticsEnabled;
                sqldb.AvailabilityGroupName = db.AvailabilityGroupName;
                sqldb.BrokerEnabled = db.BrokerEnabled;
                sqldb.CaseSensitive = db.CaseSensitive;
                sqldb.ChangeTrackingAutoCleanUp = db.ChangeTrackingAutoCleanUp;
                sqldb.ChangeTrackingEnabled = db.ChangeTrackingEnabled;
                sqldb.ChangeTrackingRetentionPeriod = db.ChangeTrackingRetentionPeriod;
                sqldb.CloseCursorsOnCommitEnabled = db.CloseCursorsOnCommitEnabled;
                sqldb.Collation = db.Collation;
                sqldb.ConcatenateNullYieldsNull = db.ConcatenateNullYieldsNull;
                sqldb.CreateDate = db.CreateDate;
                sqldb.DatabaseGuid = db.DatabaseGuid;
                sqldb.DatabaseOwnershipChaining = db.DatabaseOwnershipChaining;
                sqldb.DatabaseSnapshotBaseName = db.DatabaseSnapshotBaseName;
                sqldb.DataSpaceUsage = db.DataSpaceUsage;
                sqldb.DateCorrelationOptimization = db.DateCorrelationOptimization;
                sqldb.DboLogin = db.DboLogin;
                sqldb.DefaultFileGroup = db.DefaultFileGroup;
                sqldb.DefaultFileStreamFileGroup = db.DefaultFileStreamFileGroup;
                sqldb.DefaultFullTextCatalog = db.DefaultFullTextCatalog;
                sqldb.DefaultFullTextLanguage = db.DefaultFullTextLanguage.Name;
                sqldb.DefaultLanguage = db.DefaultLanguage.Name;
                sqldb.DefaultSchema = db.DefaultSchema;
                sqldb.EncryptionEnabled = db.EncryptionEnabled;
                sqldb.FilestreamDirectoryName = db.FilestreamDirectoryName;
                sqldb.HonorBrokerPriority = db.HonorBrokerPriority;
                sqldb.ID = db.ID;
                sqldb.IndexSpaceUsage = db.IndexSpaceUsage;
                sqldb.IsAccessible = db.IsAccessible;
                sqldb.IsDatabaseSnapshot = db.IsDatabaseSnapshot;
                sqldb.IsDatabaseSnapshotBase = db.IsDatabaseSnapshotBase;
                sqldb.IsFullTextEnabled = db.IsFullTextEnabled;
                sqldb.IsMailHost = db.IsMailHost;
                sqldb.IsManagementDataWarehouse = db.IsManagementDataWarehouse;
                sqldb.IsMirroringEnabled = db.IsMirroringEnabled;
                sqldb.IsParameterizationForced = db.IsParameterizationForced;
                sqldb.IsReadCommittedSnapshotOn = db.IsReadCommittedSnapshotOn;
                sqldb.IsSystemObject = db.IsSystemObject;
                sqldb.IsUpdateable = db.IsUpdateable;
                sqldb.IsVarDecimalStorageFormatEnabled = db.IsVarDecimalStorageFormatEnabled;
                sqldb.LastBackupDate = db.LastBackupDate;
                sqldb.LastDifferentialBackupDate = db.LastDifferentialBackupDate;
                sqldb.LastLogBackupDate = db.LastLogBackupDate;
                sqldb.LocalCursorsDefault = db.LocalCursorsDefault;
                sqldb.MirroringFailoverLogSequenceNumber = db.MirroringFailoverLogSequenceNumber;
                sqldb.MirroringID = db.MirroringID;
                sqldb.MirroringPartner = db.MirroringPartner;
                sqldb.MirroringPartnerInstance = db.MirroringPartnerInstance;
                sqldb.MirroringRedoQueueMaxSize = db.MirroringRedoQueueMaxSize;
                sqldb.MirroringRoleSequence = db.MirroringRoleSequence;
                sqldb.MirroringSafetySequence = db.MirroringSafetySequence;
                sqldb.MirroringTimeout = db.MirroringTimeout;
                sqldb.MirroringWitness = db.MirroringWitness;
                sqldb.Name = db.Name;
                sqldb.NestedTriggersEnabled = db.NestedTriggersEnabled;
                sqldb.NumericRoundAbortEnabled = db.NumericRoundAbortEnabled;
                sqldb.Owner = db.Owner;
                sqldb.PrimaryFilePath = db.PrimaryFilePath;
                sqldb.QuotedIdentifiersEnabled = db.QuotedIdentifiersEnabled;
                sqldb.ReadOnly = db.ReadOnly;
                sqldb.RecoveryForkGuid = db.RecoveryForkGuid;
                sqldb.RecursiveTriggersEnabled = db.RecursiveTriggersEnabled;
                sqldb.ServiceBrokerGuid = db.ServiceBrokerGuid;
                sqldb.Size = db.Size;
                sqldb.SpaceAvailable = db.SpaceAvailable;
                sqldb.Trustworthy = db.Trustworthy;
                sqldb.TwoDigitYearCutoff = db.TwoDigitYearCutoff;
                sqldb.UserName = db.UserName;
                sqldb.Version = db.Version;
                sqldb.ApplicationRoles = FillApplicationRoles(db.ApplicationRoles);
                sqldb.Assemblies=FillAssemblies( db.Assemblies);
                sqldb.Events= FillEvents(db.Events);
                sqldb.StoredProcedures = FillStoreProcedures(db.StoredProcedures);
                sqldb.Tables=  FillTables(db.Tables);
                sqldb.Roles=  FillRoles( db.Roles);
                sqldb.LogFiles=  FillLogFile( db.LogFiles);
                sqldb.Users= FillUsers(db.Users);
                lstDb.Add(sqldb);

            }
            return lstDb.ToArray();
        }

        private SQLUser[] FillUsers(UserCollection userCollection)
        {
            List<SQLUser> lstUser = new List<SQLUser>();
            foreach(User usr in userCollection)
            {
                SQLUser sqlUsr = new SQLUser();
                sqlUsr.AuthenticationType= usr.AuthenticationType.ToString();
                sqlUsr.CreateDate = usr.CreateDate;
                sqlUsr.DateLastModified = usr.DateLastModified;
                sqlUsr.DefaultLanguage = usr.DefaultLanguage.Name;
                sqlUsr.DefaultSchema = usr.DefaultSchema;
                sqlUsr.HasDBAccess = usr.HasDBAccess;
                sqlUsr.ID = usr.ID;
                sqlUsr.IsSystemObject = usr.IsSystemObject;
                sqlUsr.Login = usr.Login;
                sqlUsr.LoginType = usr.LoginType.ToString();
                sqlUsr.Name = usr.Name;
                sqlUsr.UserType = usr.UserType.ToString();
                lstUser.Add(sqlUsr);
                
            }
            return lstUser.ToArray();
        }

        private SQLLogFile[] FillLogFile(LogFileCollection logFileCollection)
        {
            List<SQLLogFile> lstLogFile = new List<SQLLogFile>();
            foreach (LogFile lf in logFileCollection)
            {
                SQLLogFile sqlLf = new SQLLogFile();
                sqlLf.FileName= lf.FileName;
                sqlLf.Growth = lf.Growth;
                sqlLf.GrowthType = lf.GrowthType.ToString();
                sqlLf.ID = lf.ID;
                sqlLf.IsOffline = lf.IsOffline;
                sqlLf.IsReadOnly = lf.IsReadOnly;
                sqlLf.IsReadOnlyMedia = lf.IsReadOnlyMedia;
                sqlLf.IsSparse = lf.IsSparse;
                sqlLf.MaxSize = lf.MaxSize;
                sqlLf.Name = lf.Name;
                sqlLf.Size = lf.Size;
                sqlLf.VolumeFreeSpace = lf.VolumeFreeSpace;
                lstLogFile.Add(sqlLf);
            }
            return lstLogFile.ToArray();
        }

        private SQLRole[] FillRoles(DatabaseRoleCollection databaseRoleCollection)
        {
            List<SQLRole> lstRole = new List<SQLRole>();
            foreach (DatabaseRole dr in databaseRoleCollection)
            {
                SQLRole sqlRole = new SQLRole();
                sqlRole.CreateDate= dr.CreateDate;
                sqlRole.DateLastModified = dr.DateLastModified;
                sqlRole.ID = dr.ID;
                sqlRole.IsFixedRole = dr.IsFixedRole;
                sqlRole.Name = dr.Name;
                sqlRole.Owner = dr.Owner;
                lstRole.Add(sqlRole);
                
            }
            return lstRole.ToArray();
        }

        private SQLTable[] FillTables(TableCollection tableCollection)
        {
            List<SQLTable> lstTable = new List<SQLTable>();
            foreach (Table tbl in tableCollection)
            {
                if (tbl.IsSystemObject)
                    continue;
                OnSQLEvent("Load table " + tbl.Parent.Name + " - " + tbl.Name + "  Informations");
                SQLTable sqlTbl = new SQLTable();
                sqlTbl.AnsiNullsStatus = tbl.AnsiNullsStatus;
                sqlTbl.ChangeTrackingEnabled = tbl.ChangeTrackingEnabled;
                sqlTbl.CreateDate = tbl.CreateDate;
                sqlTbl.DataSpaceUsed = tbl.DataSpaceUsed;
                sqlTbl.DateLastModified = tbl.DateLastModified;
                sqlTbl.FakeSystemTable = tbl.FakeSystemTable;
                sqlTbl.FileGroup = tbl.FileGroup;
                sqlTbl.FileStreamFileGroup = tbl.FileStreamFileGroup;
                sqlTbl.FileStreamPartitionScheme = tbl.FileStreamPartitionScheme;
                sqlTbl.FileTableDirectoryName = tbl.FileTableDirectoryName;
                sqlTbl.FileTableNameColumnCollation = tbl.FileTableNameColumnCollation;
                sqlTbl.FileTableNamespaceEnabled = tbl.FileTableNamespaceEnabled;
                sqlTbl.HasAfterTrigger = tbl.HasAfterTrigger;
                sqlTbl.HasClusteredIndex = tbl.HasClusteredIndex;
                sqlTbl.HasCompressedPartitions = tbl.HasCompressedPartitions;
                sqlTbl.HasDeleteTrigger = tbl.HasDeleteTrigger;
                sqlTbl.HasIndex = tbl.HasIndex;
                sqlTbl.HasInsertTrigger = tbl.HasInsertTrigger;
                sqlTbl.HasInsteadOfTrigger = tbl.HasInsteadOfTrigger;
                sqlTbl.HasUpdateTrigger = tbl.HasUpdateTrigger;
                sqlTbl.ID = tbl.ID;
                sqlTbl.IndexSpaceUsed = tbl.IndexSpaceUsed;
                sqlTbl.IsFileTable = tbl.IsFileTable;
                sqlTbl.IsIndexable = tbl.IsIndexable;
                sqlTbl.IsPartitioned = tbl.IsPartitioned;
                sqlTbl.IsSchemaOwned = tbl.IsSchemaOwned;
                sqlTbl.IsSystemObject = tbl.IsSystemObject;
                sqlTbl.IsVarDecimalStorageFormatEnabled = tbl.IsVarDecimalStorageFormatEnabled;
                sqlTbl.MaximumDegreeOfParallelism = tbl.MaximumDegreeOfParallelism;
                sqlTbl.Name = tbl.Name;
                sqlTbl.OnlineHeapOperation = tbl.OnlineHeapOperation;
                sqlTbl.Owner = tbl.Owner;
                sqlTbl.PartitionScheme = tbl.PartitionScheme;
                sqlTbl.QuotedIdentifierStatus = tbl.QuotedIdentifierStatus;
                sqlTbl.Replicated = tbl.Replicated;
                sqlTbl.RowCount = tbl.RowCount;
                sqlTbl.Schema = tbl.Schema;
                sqlTbl.TextFileGroup = tbl.TextFileGroup;
                sqlTbl.TrackColumnsUpdatedEnabled = tbl.TrackColumnsUpdatedEnabled;
                sqlTbl.Indexes=FillIndexes(tbl.Indexes);
                sqlTbl.ForeignKeys=FillForeignKeys( tbl.ForeignKeys);
                sqlTbl.Columns=FillColumns( tbl.Columns);
                lstTable.Add(sqlTbl);
            }
            return lstTable.ToArray();
        }

        private SQLColumn[] FillColumns(ColumnCollection columnCollection)
        {
            List<SQLColumn> lstCol = new List<SQLColumn>();
            foreach (Column col in columnCollection)
            {
                
                SQLColumn sqlCol = new SQLColumn();
                sqlCol.AnsiPaddingStatus= col.AnsiPaddingStatus;
                sqlCol.Collation = col.Collation;
                sqlCol.Computed = col.Computed;
                sqlCol.ComputedText = col.ComputedText;
                sqlCol.DataType = col.DataType.Name;
                sqlCol.SqlDataType = col.DataType.SqlDataType.ToString();
                sqlCol.Default = col.Default;
                sqlCol.DefaultSchema = col.DefaultSchema;
                sqlCol.ID = col.ID;
                sqlCol.Identity = col.Identity;
                sqlCol.IdentityIncrement = col.IdentityIncrement;
                sqlCol.IdentitySeed = col.IdentitySeed;
                sqlCol.InPrimaryKey = col.InPrimaryKey;
                sqlCol.IsColumnSet = col.IsColumnSet;
                sqlCol.IsDeterministic = col.IsDeterministic;
                sqlCol.IsFileStream = col.IsFileStream;
                sqlCol.IsForeignKey = col.IsForeignKey;
                sqlCol.IsFullTextIndexed = col.IsFullTextIndexed;
                sqlCol.IsPersisted = col.IsPersisted;
                sqlCol.IsPrecise = col.IsPrecise;
                sqlCol.IsSparse = col.IsSparse;
                sqlCol.Name = col.Name;
                sqlCol.NotForReplication = col.NotForReplication;
                sqlCol.Nullable = col.Nullable;
                sqlCol.Rule = col.Rule;
                sqlCol.RuleSchema = col.RuleSchema;

                lstCol.Add(sqlCol);
            }
            return lstCol.ToArray();
        }

        private SQLForeignKey[] FillForeignKeys(ForeignKeyCollection foreignKeyCollection)
        {
            List<SQLForeignKey> lstFk = new List<SQLForeignKey>();
            foreach (ForeignKey fk in foreignKeyCollection)
            {
                SQLForeignKey sqlFK = new SQLForeignKey();
                List<SQLForeignKeyColumn> lstFKC = new List<SQLForeignKeyColumn>();
                foreach (ForeignKeyColumn fkc in fk.Columns)
                {
                    SQLForeignKeyColumn sqlFkc = new SQLForeignKeyColumn();
                    sqlFkc.ID=fkc.ID;
                    sqlFkc.Name = fkc.Name;
                    sqlFkc.ReferencedColumn = fkc.ReferencedColumn;
                    lstFKC.Add(sqlFkc);
                }
                sqlFK.ForeignKeyColumns = lstFKC.ToArray();
                sqlFK.CreateDate = fk.CreateDate;
                sqlFK.DateLastModified = fk.DateLastModified;
                sqlFK.DeleteAction = fk.DeleteAction.ToString();
                sqlFK.ID = fk.ID;
                sqlFK.IsChecked = fk.IsChecked;
                sqlFK.IsEnabled = fk.IsEnabled;
                sqlFK.IsFileTableDefined = fk.IsFileTableDefined;
                sqlFK.IsSystemNamed = fk.IsSystemNamed;
                sqlFK.Name = fk.Name;
                sqlFK.NotForReplication = fk.NotForReplication;
                sqlFK.ReferencedKey = fk.ReferencedKey;
                sqlFK.ReferencedTable = fk.ReferencedTable;
                sqlFK.ReferencedTableSchema = fk.ReferencedTableSchema;
                sqlFK.ScriptReferencedTable = fk.ScriptReferencedTable;
                sqlFK.ScriptReferencedTableSchema = fk.ScriptReferencedTableSchema;
                sqlFK.UpdateAction = fk.UpdateAction.ToString();
                lstFk.Add(sqlFK);
            }
            return lstFk.ToArray();
        }

        private SQLIndex[] FillIndexes(IndexCollection indexCollection)
        {
            List<SQLIndex> lstIndex = new List<SQLIndex>();
            foreach (Index i in indexCollection)
            { 
                
            }
            return lstIndex.ToArray();
        }

        private SQLStoredProcedure[] FillStoreProcedures(StoredProcedureCollection storedProcedureCollection)
        {
            List<SQLStoredProcedure> lstStoreProc = new List<SQLStoredProcedure>();
            foreach (StoredProcedure sp in storedProcedureCollection)
            {
                if (sp.IsSystemObject)
                {
                    continue;
                }
                OnSQLEvent("Load Store Procedure " + sp.Parent.Name + " - " + sp.Name + "  Informations");
                SQLStoredProcedure sqlsp = new SQLStoredProcedure();
                sqlsp.AnsiNullsStatus= sp.AnsiNullsStatus;
                sqlsp.AssemblyName = sp.AssemblyName;
                sqlsp.ClassName = sp.ClassName;
                sqlsp.CreateDate = sp.CreateDate;
                sqlsp.DateLastModified = sp.DateLastModified;
                sqlsp.ForReplication = sp.ForReplication;
                sqlsp.ID = sp.ID;
                sqlsp.ImplementationType = sp.ImplementationType.ToString();
                sqlsp.IsEncrypted = sp.IsEncrypted;
                sqlsp.Name = sp.Name;
                sqlsp.MethodName = sp.MethodName;
                if (sp.TextMode)
                {
                    sqlsp.TextBody = sp.TextBody;
                    sqlsp.TextHeader = sp.TextHeader;
                }
                sqlsp.TextMode = sp.TextMode;
                sqlsp.Startup = sp.Startup;
                sqlsp.Schema = sp.Schema;
                List<SQLStoredProcedureParameter> lstsspp = new List<SQLStoredProcedureParameter>();
                foreach (StoredProcedureParameter spp in sp.Parameters)
                {
                    SQLStoredProcedureParameter sqlspp = new SQLStoredProcedureParameter();
                    sqlspp.DataType= spp.DataType.SqlDataType.ToString();
                    sqlspp.DefaultValue = spp.DefaultValue;
                    sqlspp.ID = spp.ID;
                    sqlspp.IsCursorParameter = spp.IsCursorParameter;
                    sqlspp.IsOutputParameter = spp.IsOutputParameter;
                    sqlspp.IsReadOnly = spp.IsReadOnly;
                    sqlspp.Name = spp.Name;
                    lstsspp.Add(sqlspp);
                }
                sqlsp.Parameters = lstsspp.ToArray();
                lstStoreProc.Add(sqlsp);
            }
            return lstStoreProc.ToArray();
        }

        private SQLEvent[] FillEvents(DatabaseEvents databaseEvents)
        {
            List<SQLEvent> lstEvent = new List<SQLEvent>();
            return lstEvent.ToArray();
        }

        private SQLAssembly[] FillAssemblies(SqlAssemblyCollection sqlAssemblyCollection)
        {
            List<SQLAssembly> lstAssembly = new List<SQLAssembly>();
            return lstAssembly.ToArray();
        }

        private SQLApplicationRole[] FillApplicationRoles(ApplicationRoleCollection applicationRoleCollection)
        {
            List<SQLApplicationRole> lstApplicationRole = new List<SQLApplicationRole>();
            return lstApplicationRole.ToArray();
        }

    }
}
